//
//  : //  DataCollector
//
//  Created by jiro on 12/07/07.
//  Copyright 2012 jiro music. All rights reserved.
//

#import "CurrencyExchangeGmo.h"

#import "DbConstants.h"
#import "DbUtility.h"
#import "../../Commons.h"
#import "../Nodes.h"


NSString* CurrencyExchangeGmo::tableName()
{
	return kText_CurrencyExchangeGmoTable;
}

NSString* CurrencyExchangeGmo::queryCreateTable()
{
	NSString* s = [ NSString stringWithFormat:
		@"create table %@ ( %@ %@, %@ %@, %@ %@, %@ %@, %@ %@, %@ %@, %@ %@, %@( %@, %@, %@ ) );",
			CurrencyExchangeGmo::tableName(), 
			kText_Code, kText_Text,
			kText_TargetCurrencyCode, kText_Text,
			kText_ExchangeDate, kText_TimestampWithTimeZone,
			kText_OpenPrice, kText_Real,
			kText_HighPrice, kText_Real,
			kText_LowPrice, kText_Real, 
			kText_ClosePrice, kText_Real,
			kText_PrimaryKey, kText_Code, kText_TargetCurrencyCode, kText_ExchangeDate
	];
	return s;
}

NSString* CurrencyExchangeGmo::querySelectAll( const std::vector< ClauseNode >& clauses )
{
	NSString* s = DbTableEntry::querySelectAll( CurrencyExchangeGmo::tableName(), clauses );
	return s;
}

NSString* CurrencyExchangeGmo::querySelect( const std::vector< FieldNode >& fieldNodes, const std::vector< ClauseNode >& clauses )
{
	NSString* s = DbTableEntry::querySelect( fieldNodes, CurrencyExchangeGmo::tableName(), clauses );
	return s;
}

CurrencyExchangeGmo::CurrencyExchangeGmo()
{
}

CurrencyExchangeGmo::CurrencyExchangeGmo( const DbExchangeTableEntry& entry  )
	: DbExchangeTableEntry::DbExchangeTableEntry( entry )
{
}

CurrencyExchangeGmo::CurrencyExchangeGmo( std::vector< const char* > values )
	: DbExchangeTableEntry::DbExchangeTableEntry( values )
{
}

NSString* CurrencyExchangeGmo::sqlInsertStatement() const
{
	NSString* s = [ NSString stringWithFormat:
		@"insert into %@ values %@;",
		CurrencyExchangeGmo::tableName(), queryInsertValue() ];
	return s;
}

NSString* CurrencyExchangeGmo::queryInsertValue() const
{
	return CurrencyExchangeJa::queryInsertInitialValues();
}

std::vector< NSString* > CurrencyExchangeGmo::queriesSelectAll( NSString* currnecyCode, NSString* targetCurrencyCode, NSDate* startDate, NSDate* endDate, double intervalSec )
{
	if ( intervalSec == 0 )
	{
		intervalSec = [ endDate timeIntervalSinceDate: startDate ];
	}

	std::vector< NSString* > queries;
	NSDate* date = [ NSDate dateWithTimeInterval: 0 sinceDate: startDate ];
	while( [ date timeIntervalSinceDate: endDate ] <= 0 )
	{
//		NSLog( @"%@", date );

		//	---- conditions
		ExpressionNode startDateCondition( FieldNode( kText_ExchangeDate ), BinaryOperatorNode::Type_LessOrEqual, DateValueNode( date ) );
		ExpressionNode endDateCondition( FieldNode( kText_ExchangeDate ), BinaryOperatorNode::Type_GreaterOrEqual, DateValueNode( [ date dateByAddingTimeInterval: - intervalSec + secondsForOneMinuet ] ) );
		ExpressionNode startEndDateConditions( startDateCondition, BinaryOperatorNode::Type_And, endDateCondition );; 
		ExpressionNode dateCondition1( FieldNode( kText_ExchangeDate ), BinaryOperatorNode::Type_GreaterOrEqual, DateValueNode( [ date dateByAddingTimeInterval: - intervalSec + secondsForOneMinuet ] ) );
		ExpressionNode dateCondition2( FieldNode( kText_ExchangeDate ), BinaryOperatorNode::Type_LessOrEqual, DateValueNode( date ) );
		ExpressionNode dateConditions( dateCondition1, BinaryOperatorNode::Type_And, dateCondition2 );
		ExpressionNode currencyCodeCondition( FieldNode( kText_Code ), BinaryOperatorNode::Type_Equal, StringValueNode( currnecyCode ) );
		ExpressionNode targetCurrencyCodeCondition( FieldNode( kText_TargetCurrencyCode ), BinaryOperatorNode::Type_Equal, StringValueNode( targetCurrencyCode ) );
		ExpressionNode currencyCodeConditions( currencyCodeCondition, BinaryOperatorNode::Type_And, targetCurrencyCodeCondition );
		//	----

		//	---- sub queries
		ExpressionNode conditions;
		WhereNode where;
		LimitNode limit( 1 );
		OrderByNode orderBy( kText_ExchangeDate, YES );

		//	---- primary keys sub query
		orderBy = OrderByNode( kText_ExchangeDate, NO );
		NSString* primaryKeysSelectAlias = @"d";
		conditions = ExpressionNode( startEndDateConditions, BinaryOperatorNode::Type_And, currencyCodeConditions );
		where = WhereNode( conditions );
		
		std::vector< FieldNode > fields( 3 );
		fields[ 0 ] = FieldNode( kText_Code );
		fields[ 1 ] = FieldNode( kText_TargetCurrencyCode );
		fields[ 2 ] = FieldNode( kText_ExchangeDate );
		
		std::vector< ClauseNode > clauses( 3 );
		clauses[ 0 ] = where;
		clauses[ 1 ] = orderBy;
		clauses[ 2 ] = limit;

		NSString* primaryKeysQuery = [ NSString
			stringWithFormat: @"( %@ ) as %@", 
				CurrencyExchangeGmo::querySelect( fields, clauses ),
				primaryKeysSelectAlias ];
		primaryKeysQuery = [ primaryKeysQuery stringByReplacingOccurrencesOfString: @";" withString: @"" ];
		//	----
		
		
		//	---- start price sub queries
		NSString* startPriceSelectAlias = @"a";
		orderBy = OrderByNode( kText_ExchangeDate, YES );
		conditions = ExpressionNode( startEndDateConditions, BinaryOperatorNode::Type_And, currencyCodeConditions );
		where = WhereNode( conditions );
		
		fields.clear();
		fields.resize( 4 );
		fields[ 0 ] = FieldNode( kText_Code );
		fields[ 1 ] = FieldNode( kText_TargetCurrencyCode );
		fields[ 2 ] = FieldNode( kText_ExchangeDate );
		fields[ 3 ] = FieldNode( kText_OpenPrice );

		clauses[ 0 ] = where;
		clauses[ 1 ] = orderBy;
		clauses[ 2 ] = limit;

		NSString* startPriceQuery = [ NSString
			stringWithFormat: @"( %@ ) as %@", 
				CurrencyExchangeGmo::querySelect( fields, clauses ),
				startPriceSelectAlias ];
		startPriceQuery = [ startPriceQuery stringByReplacingOccurrencesOfString: @";" withString: @"" ];
		//	---- 

		//	---- high & low price sub queries 
		conditions = ExpressionNode( dateConditions, BinaryOperatorNode::Type_And, currencyCodeConditions );
		where = WhereNode( conditions );
		
		fields.clear();
		fields.resize( 2 );
		fields[ 0 ] = FieldNode( nil, [ NSString stringWithFormat: @" %@( %@ )", kText_Max, kText_HighPrice ], kText_HighPrice );
		fields[ 1 ] = FieldNode( nil, [ NSString stringWithFormat: @" %@( %@ )", kText_Min, kText_LowPrice ], kText_LowPrice );
		
		clauses.clear();
		clauses.resize( 1 );
		clauses[ 0 ] = where;

		NSString* highAndLowPriceSelectAlias = @"b";
		NSString* highAndLowPriceQuery = [ NSString 
			stringWithFormat: @"( %@ ) as %@",
				CurrencyExchangeGmo::querySelect( fields, clauses ), 
				highAndLowPriceSelectAlias ];
		highAndLowPriceQuery = [ highAndLowPriceQuery stringByReplacingOccurrencesOfString: @";" withString: @"" ];
		//	----
		
		//	---- end price sub queries
		NSString* endPriceSelectAlias = @"c";
		conditions = ExpressionNode( startEndDateConditions, BinaryOperatorNode::Type_And, currencyCodeConditions );
		where = WhereNode( conditions );
		orderBy = OrderByNode( kText_ExchangeDate, NO );
		
		fields.clear();
		fields.resize( 1 );
		fields[ 0 ] = FieldNode( kText_ClosePrice );
		
		clauses.clear();
		clauses.resize( 3 );
		clauses[ 0 ] = where;
		clauses[ 1 ] = orderBy;
		clauses[ 2 ] = limit;

		NSString* endPriceQuery = [ NSString
			stringWithFormat: @"( %@ ) as %@", 
				CurrencyExchangeGmo::querySelect( fields, clauses ),
				endPriceSelectAlias ];
		endPriceQuery = [ endPriceQuery stringByReplacingOccurrencesOfString: @";" withString: @"" ];
		//	----

		//	---- final query
		NSString* selects = [ NSString 
			stringWithFormat: @" %@, %@, %@, %@ ",
			startPriceQuery, endPriceQuery, highAndLowPriceQuery, primaryKeysQuery ];
		FieldNode codeField( primaryKeysSelectAlias, kText_Code, NULL, false );
		FieldNode targetCurrencyCodeField( primaryKeysSelectAlias, kText_TargetCurrencyCode, NULL, false );
		FieldNode exchangeDateField( primaryKeysSelectAlias, kText_ExchangeDate, NULL, false );
		FieldNode startPriceField( startPriceSelectAlias, kText_OpenPrice, NULL, false );
		FieldNode highPriceField( highAndLowPriceSelectAlias, kText_HighPrice, NULL, false );
		FieldNode lowPriceField( highAndLowPriceSelectAlias, kText_LowPrice, NULL, false );
		FieldNode endPriceField( endPriceSelectAlias, kText_ClosePrice, NULL, false );
		NSString* query = [ NSString 
			stringWithFormat: @"select %@, %@, %@, %@, %@, %@, %@ from %@;",
				codeField.query(), targetCurrencyCodeField.query(), exchangeDateField.query(),
				startPriceField.query(), highPriceField.query(), lowPriceField.query(), endPriceField.query(),
				selects ];
		//	----

		queries.push_back( query );
		date = [ date dateByAddingTimeInterval: intervalSec ];
//		NSLog( @"%@", query );
	}
//	double d = [ date timeIntervalSinceDate: endDate ];
	return queries;
}
